ActiveReports 11
DataSet Dialog
ActiveReports 11 > ActiveReports User Guide > Concepts > Page Report/RDL Report Concepts > Data Sources and Datasets > DataSet Dialog

You can access the DataSet dialog from the Report Explorer by doing one of the following:

The DataSet dialog provides the following pages where you can set dataset properties:

General

The General page of the DataSet dialog is where you can set the Name of the dataset.

Name: In the Name field, you can enter a name for the dataset. By default, the name is set to DataSet1. The name of the dataset appears in the tree view of the Report Explorer. It is also used to call the dataset in code so it should be unique within the report.

Query

The Query page of the DataSet dialog is where you set the SQL query, stored procedure or table to define the data you want to fetch in the dataset of your report.

Command type: You can choose from the three enumerated command types.

Type Description
Text Choose Text if you want to write a SQL query to retrieve data.
StoredProcedure

Choose StoredProcedure if you want to use a stored procedure.

TableDirect Choose TableDirect if you want to return all rows and columns from one or more tables.

Query: Based on the command type you select above, you can set the query string in this field.

Note:

  • If you select the TableDirect command type, you may need to use escape characters or qualifying characters in case any of the table names include special characters.
  • For Oracle data source, you should use the Text SQL query as command type instead of StoredProcedure to call a stored procedure.
  • Specify the calculated index for arrays in a JSONPath expression in the following ways:

    • To obtain the last entry in an array, use -1: in square brackets. For example, use $..book[-1:].
    • To obtain evaluated expressions correctly, the field names in square brackets should be in single quotes. For example, use $..book[0]['category','author'].

Timeout: You can set the number of seconds that you want the report server to wait for the query to return the data before it stops trying.

Options

The Options page is where you select one of the various options available to the dataset.

CaseSensitivity: Set this value to Auto, True, or False to indicate whether to make distinctions between upper and lower case letters. Auto, the default value, causes the report server to get the value from the data provider. If the data provider does not set the value, the report runs without case sensitivity.

Collation: Choose from Default or a country from the list to indicate which collation sequence to use to sort data. The Default value causes the report server to get the value from the data provider. If the data provider does not set the value, the report uses the server locale. This is important with international data, as the sort order for different languages can be different from the machine sort.

KanaTypeSensitivity: Set this value to Auto, True, or False with Japanese data to indicate whether distinctions are made between Hiragana and Katakana kana types. Auto, the default value, causes the report server to get the value from the data provider. If the data provider does not set the value, the report runs without kana type sensitivity.

WidthSensitivity: Set this value to Auto, True, or False with Japanese data to indicate whether distinctions are made between single-byte (half-width) characters and double-byte (full-width) characters. Auto, the default value, causes the report server to get the value from the data provider. If the data provider does not set the value, the report runs without width sensitivity.

AccentSensitivity: Set this value to Auto, True, or False to indicate whether distinctions are made between accented and unaccented letters. Auto, the default value, causes the report server to get the value from the data provider. If the data provider does not set the value, the report runs without accent sensitivity.

Fields

The Fields page of the DataSet dialog populates automatically for OleDb, ODBC, SQL, JSON, XML, and Oracle data providers. To see a list of fields in the Name and Value columns of the Fields page, enter a valid query, table name, or stored procedure on the Query page.

Note: The dataset for a CSV data source is automatically created on adding the data source. You can edit the name of the data set on the General page and modify the fields on the Fields page.

You can edit the populated fields, delete them by using the Remove (X) icon, or add new ones by using the Add (+) icon above the Fields list. Any fields you add in this list show up in the Report Explorer tree view and you can drag and drop them onto the design surface. The field name must be unique within the dataset.

When working with Fields, the meaning of the value varies depending on the data source type. In most cases this is simply the name of the field. The following table describes the meaning of the field value and gives some examples of how to use the value.

Data Provider Description Example
SQL, Oracle, OleDb The field value is the name of a field returned by the query. Query:
OrderQuantity
FirstName
Dataset The field value can be the name of a field in the DataTable specified by the query. You can also use DataRelations in a DataSet, specify the name of the relation followed by a period and then the name of a field in the related DataTable. Query:
Quantity
OrdersToOrderDetails.CustomerID
XML The field value is an XPath expression that returns a value when evaluated with the query. Query:
Statistics/Game/TeamName
JSON The field value is a JSONPath expression that returns a value when evaluated with the query. Query:
$.Statistics.Game[*].TeamName
Object The field value can be the name of a property of the object contained in the collection returned by the data provider. You may also use properties available for the object returned from a property. Query:
Quantity
Order.Customer.FirstName
CSV The field value is the name of a field returned by each column specified in the connection string. Connection string:
Path=C:\\Data\\FixedWidth.csv;Locale=en-US;TextQualifier=";ColumnsSeparator=,;RowsSeparator=\r\n;HasHeaders=True

 

Parameters

The Parameters page of the Dataset dialog is where you can pass a Report Parameter into the parameter you enter in the Query page. Enter a Name that matches the name of the Report Parameter and a Value for each parameter in this page.

The Value of a parameter can be a static value or an expression referring to an object within the report. The Value cannot refer to a report control or field.

Filters

The Filters page of the Dataset dialog allows you to filter data after it is returned from the data source. This is useful when you have a data source (such as XML) that does not support query parameters.

A filter is composed of three fields:

Expression: Type or use the expression editor to provide the expression on which to filter data.

Operator: Select from the following operators to decide how to compare the expression to the left with the value to the right:

Value: Enter a value to compare with the expression on the left based on the selected operator. For multiple values used with the Between operator, the lower two value boxes are enabled.

Values: When you choose the In operator, you can enter as many values as you need in this list.

See Also

How To